/**
 * Database Interface for MultipleNestingTagging Library
 */
package org.chungyan5.multiplenestingtagging.mobile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.chungyan5.multiplenestingtagging.DbConn;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.os.Environment;
import android.util.Log;

/**
 * @author yan
 *
 */
public class DbConnImp implements DbConn {

	private Context context;
	
	private DatabaseHelper dbHelper;

	/**
	 * 
	 */
	public DbConnImp(Context context) {
		this.context = context;
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, "Mnt.db", null, 1);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("create table " + TABLE_TAG + "(" + TABLE_TAG_COL_TAG_PTR 
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TABLE_TAG_COL_TAG_NAME + " TEXT);");
			db.execSQL("create table " + TABLE_ITEM + "(" + TABLE_ITEM_COL_ITEM_PTR 
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TABLE_ITEM_COL_ITEM_NAME + " TEXT);");
			db.execSQL("create table " + TABLE_TAGS_ITEMS + "(" + TABLE_TAGS_ITEMS_COL_PTR 
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ TABLE_TAGS_ITEMS_COL_ITEM_PTRS + " INTEGER, " 
					+ TABLE_TAGS_ITEMS_COL_TAG_PTRS + " INTEGER);");
			db.execSQL("create table " + TABLE_TAG_ITEM_REL + "(" + TABLE_TAG_ITEM_REL_COL_PTRS + " INTEGER PRIMARY KEY, " 
					+ TABLE_TAG_ITEM_REL_COL_TAG_PTRS + " INTEGER,"
					+ TABLE_TAG_ITEM_REL_COL_ITEM_PTRS + " INTEGER,"
					+ TABLE_TAG_ITEM_REL_COL_ITEM_NAME +  " TEXT);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			
		}
		
	}
	
	/**
	 * chk DB, if !existing, create the whole structure, otherwise skip to create new one
	 */
	public void open() {
		dbHelper = new DatabaseHelper(context);
	}
	
	public void close() {
		dbHelper.close();
	}

	@Override
	public boolean export() {
		try {
//			File[] files = new File[2];
			
			// get db file ===============================================
			File dbFile =
				new File(Environment.getDataDirectory() + "/data/org.chungyan5.multiplenestingtagging.mobile/databases/Mnt.db");
//			files[0] = dbFile;

			// prepare db directory and db file inside sdcard ==================================
			File sdDir = new File(Environment.getExternalStorageDirectory(), "Mnt");
			if (!sdDir.exists())
				if (!sdDir.mkdirs()) throw new IOException("cannot create Mnt/ inside sdcard");
			File sdFile = new File(sdDir, dbFile.getName());
			sdFile.createNewFile();
//			files[1] = sdFile;
			
			fileCopy(dbFile, sdFile);
			return true;
		} catch (IOException e) {
			Log.w("yan", e.getMessage());
			return false;
		}
	}
	
	@Override
	public boolean retore() {
		try {
			File dbFile =
				new File(Environment.getDataDirectory() + "/data/org.chungyan5.multiplenestingtagging.mobile/databases/Mnt.db");
			
			// prepare db directory and db file inside sdcard ==================================
			File sdDir = new File(Environment.getExternalStorageDirectory(), "Mnt");
			File sdFile = new File(sdDir, dbFile.getName());
			if (!sdFile.exists()) throw new IOException("database file for restore inside sdcard does not exist, cannot import");
			
			// get db file ===============================================
			if (!dbFile.exists()) dbFile.delete();
			dbFile.createNewFile();
			
			// copy db =====================================
			fileCopy(sdFile, dbFile);
			
			// re-connect back the db =====================================
			dbHelper.close();
			
			return true;
		} catch (IOException e) {
			Log.w("yan", e.getMessage());
			return false;
		}
	}

	private void fileCopy(File src, File dest) throws IOException {
		FileChannel inChannel = new FileInputStream(src).getChannel();
		FileChannel outChannel = new FileOutputStream(dest).getChannel();
		try {
			inChannel.transferTo(0, inChannel.size(), outChannel);
		} finally {
			if (inChannel != null)
				inChannel.close();
			if (outChannel != null)
				outChannel.close();
		}
	}

	@Override
	public void execSQL(String sql) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(sql);
	}

	public static class DbReturnDataImp implements DbReturnData {
		
		private Cursor cursor;
		
		public DbReturnDataImp(Cursor cursor) {
			this.cursor = cursor;
		}

		@Override
		public void close() {
			cursor.close();
		}

		@Override
		public int getInt(int columnIndex) {
			return cursor.getInt(columnIndex);
		}

		@Override
		public long getLong(int columnIndex) {
			return cursor.getLong(columnIndex);
		}

		@Override
		public String getString(int columnIndex) {
			return cursor.getString(columnIndex);
		}

		@Override
		public boolean moveToNext() {
			return cursor.moveToNext();
		}

		public Cursor getCursor() {
			return this.cursor;
		}
		
		public int getCount() {
			return cursor.getCount();
		}

		@Override
		public int getColumnIndex(String columnName) {
			return cursor.getColumnIndex(columnName);
		}
	}

	@Override
	public long insert(String table, Set<Entry<String, Object>> columnValue) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		Iterator<Map.Entry<String, Object>> itr = columnValue.iterator();
		while (itr.hasNext()) {
			Map.Entry<String, Object> e = itr.next();
			Object o = e.getValue();
			if (o==null) values.putNull(e.getKey());
			else {
				if (o instanceof String) {
					values.put(e.getKey(), (String) o);
				} else if (o instanceof Long) {
					values.put(e.getKey(), (Long) o);
				}				
			}
		}
		
		return db.insert(table, null, values);		// if return -ve, it is err., un-handle this issue now
	}

	public DbReturnData query(String table, String[] column, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder) {
		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		qb.setTables(table);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		
		// insert a "_id" column if Application does not provide that, since Android must be included this, otherwise exception 
		String[] addedIdColumn;
		int i;
		for (i=0;i<column.length;i++)
			if (column[i].contains("_id")) break;
		if (i==column.length) {
			addedIdColumn = new String[column.length + 1];
			String[] idColumn = {"_id"};
			System.arraycopy(column, 0, addedIdColumn, 0, column.length);
			System.arraycopy(idColumn, 0, addedIdColumn, column.length, 1);
		} else {
			addedIdColumn = new String[column.length];
			System.arraycopy(column, 0, addedIdColumn, 0, column.length);
		}
		
		// call query to Android Sqlite3 
		Cursor cursor = qb.query(db, addedIdColumn, selection, selectionArgs, groupBy, having, sortOrder);
		DbReturnDataImp dbReturnDataImp = new DbReturnDataImp(cursor);
		return dbReturnDataImp;
	}
	
	@Override
	public int update(String table, Set<Entry<String, Object>> columnValue,
			String whereClause) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		ContentValues values = new ContentValues();
		Iterator<Map.Entry<String, Object>> itr = columnValue.iterator();
		while (itr.hasNext()) {
			Map.Entry<String, Object> e = itr.next();
			Object o = e.getValue();
			if (o==null) values.putNull(e.getKey());
			else {
				if (o instanceof String) {
					values.put(e.getKey(), (String) o);
				} else if (o instanceof Long) {
					values.put(e.getKey(), (Long) o);
				}				
			}
		}
		
		int i = db.update(table, values, whereClause, null);
		return i;
	}

	@Override
	public int remove(String table, String whereClause) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		return db.delete(table, whereClause, null);
	}

}
